package com.jpf.dao;
import java.sql.*;
import java.util.*;

import javax.servlet.http.HttpServletRequest;

import com.jpf.demo.Goods;
import com.jpf.demo.Page;
import com.jpf.dao.DatabaseConnection;
public class Goodsdao {
	DatabaseConnection conn = null;
	public Goodsdao() {
		super();//调用父类的方法
		conn = new DatabaseConnection();
	}
	/**
	 * 添加方法
	 * @param goods
	 * @return
	 */
	public int insert(Goods goods) {//gid;cid;gname;brand;gimage;content;shelftime;gprice; gstock;
		String sql = "insert into goods(cid,gname,brand,gimage,content,shelftime,gprice,gstock) values(?,?,?,?,?,?,?,?)";
		Object[] param = {goods.getCid(),goods.getGname(),goods.getBrand(),goods.getGimage(),goods.getContent(),goods.getShelftime(),goods.getGprice(),goods.getGstock()};
		conn.getConnection();
		int result = conn.executeUpdate(sql, param); //取得返回结果
		conn.close();
		return result;
	}
	/**
	 * 查询方法
	 * 
	 * @param gname
	 * @return goods对象
	 */
	public List<Goods> queryGoods(String gname) {//根据商品名称查询
		List<Goods> list = new ArrayList<Goods>();
		Goods goods = null;
		String sql = "select * from goods where gname=?";
		Object[] param = { gname };
		conn.getConnection();
		ResultSet rs = conn.executeQuery(sql, param);
		try {
		   while (rs.next()) {
				goods = new Goods(rs.getInt("gid"), rs.getString("cid"), rs.getString("gname"), rs.getString("brand"),rs.getString("gimage"), rs.getString("content"), rs.getString("shelftime"), rs.getDouble("gprice"), rs.getInt("gstock"));
				list.add(goods);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			conn.close();
		}
		return list;
	}
	public Goods queryGoods(int gid) {//根据商品编号查询
		Goods goods = null;
		String sql = "select * from goods where gid=?";
		Object[] param = { gid };
		conn.getConnection();
		ResultSet rs = conn.executeQuery(sql, param);
		try {
		   if (rs.next()) {
				goods = new Goods(rs.getInt("gid"), rs.getString("cid"), rs.getString("gname"), rs.getString("brand"),rs.getString("gimage"), rs.getString("content"), rs.getString("shelftime"), rs.getDouble("gprice"), rs.getInt("gstock"));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			conn.close();
		}
		return goods;
	}
	public List<Goods> queryGoods() {//查询整个商品表
		List<Goods> goodsList=null;
		goodsList = new ArrayList<Goods>();
		Goods goods = null;
		String sql = "select * from goods";
		Object[] param = null;
		conn.getConnection();
		ResultSet rs = conn.executeQuery(sql, param);
		try {
			while (rs.next()) {
				goods = new Goods(rs.getInt("gid"), rs.getString("cid"), rs.getString("gname"), rs.getString("brand"),rs.getString("gimage"), rs.getString("content"), rs.getString("shelftime"), rs.getDouble("gprice"), rs.getInt("gstock"));
				goodsList.add(goods);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			conn.close();
		}
		return goodsList;
	}
	/**
	 * 修改方法
	 * 
	 * @param goods
	 * @return result
	 */
	public int updateGoods(Goods goods) {
		//gid;cid;gname;brand;gimage;content;shelftime;gprice; gstock;
		String sql = "update goods set cid=?,gname=?,brand=?,gimage=?,content=?,shelftime=?,gprice=?,gstock=? where gid=?";
		Object[] param = { goods.getCid(),goods.getGname(),goods.getBrand(),goods.getGimage(),goods.getContent(),goods.getShelftime(),goods.getGprice(),goods.getGstock(),goods.getGid()};
		conn.getConnection();
		int result = conn.executeUpdate(sql, param);
		conn.close();
		return result;
	}
	/**
	 * 删除方法
	 * 
	 * @param gid
	 * @return result
	 */
	public int deleteGoods(Integer gid) {
		String sql = "delete from goods where gid=?";
		Object[] param = { gid };
		conn.getConnection();
		int result = conn.executeUpdate(sql, param);
		//conn.close();					//Ajax并发请求时会因为连接关闭发生异常 
		return result;
	}
	/**
	 * 分页方法
	 * @param pageSize 每一页的行数
	 * @param pageNow 当前页
	 * @return page
	 */
	public Page<Goods> queryPage(int pageSize, int pageNow) {
		Page<Goods> page = new Page<Goods>();
		page.setPageSize(pageSize);
		page.setPageNow(pageNow);
		String sql = "select count(*) from goods";
		conn.getConnection();
		ResultSet rs = conn.executeQuery(sql, null);
		try {
			rs.next();
			Integer rowCount = rs.getInt(1);
			page.setRowCount(rowCount);
			List<Goods> pageRow = new ArrayList<Goods>();
			String preparedSql = "select * from goods limit ?,?";
			Object[] param = { page.getPageSize() *( page.getPageNow() - 1), page.getPageSize() };
			ResultSet res = conn.executeQuery(preparedSql, param);
			while (res.next()) {
				Goods goods = new Goods(res.getInt("gid"), res.getString("cid"), res.getString("gname"), res.getString("brand"),res.getString("gimage"), res.getString("content"), res.getString("shelftime"), res.getDouble("gprice"), res.getInt("gstock"));
				pageRow.add(goods);
			}
			page.setpageRow(pageRow);
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			conn.close();
		}
		return page;
	}
	public Page<Goods> queryPage(int pageSize, int pageNow,String gname) {
		System.out.print(gname);
		Page<Goods> page = new Page<Goods>();
		page.setPageSize(pageSize);
		page.setPageNow(pageNow);
		String sql = "select count(*) from goods where gname=?";
		Object[] params = { gname };
		conn.getConnection();
		ResultSet rs = conn.executeQuery(sql, params);
		try {
			rs.next();
			Integer rowCount = rs.getInt(1);
			page.setRowCount(rowCount);
			List<Goods> pageRow = new ArrayList<Goods>();
			String preparedSql = "select * from goods where gname=? limit ?,?";
			Object[] param = { gname,page.getPageSize() *( page.getPageNow() - 1), page.getPageSize() };
			ResultSet res = conn.executeQuery(preparedSql, param);
			while (res.next()) {
				Goods goods = new Goods(res.getInt("gid"), res.getString("cid"), res.getString("gname"), res.getString("brand"),res.getString("gimage"), res.getString("content"), res.getString("shelftime"), res.getDouble("gprice"), res.getInt("gstock"));
				pageRow.add(goods);
			}
			page.setpageRow(pageRow);
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			conn.close();
		}
		return page;
	}
}
